package com.shgbit.sdis.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

import org.omg.CORBA.UserException;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.shgbit.sdis.bean.DemoCity;
import com.shgbit.sdis.mapper.DemoCityMapper;


/**
 * 数据访问Demo
 * @author liulinqing
 * 上午10:59:02
 */
@Repository
public class DemoCityDao {

	@Autowired  
    private JdbcTemplate jdbcTemplate;  
	
	/** 
     * 获取城市列表 
     * @return 
     * @throws Exception 
     */  
    @Transactional(readOnly = true)  
    public List<DemoCity> getEmpytList() throws Exception{  
        List<DemoCity> cityList=jdbcTemplate.query("select ID,Name,CountryCode,District,Population from city",new DemoCityMapper());  
        return cityList;  
    }  
    
    /** 
     * 根据用户id获取城市
     * @param id 
     * @return 
     * @throws Exception 
     */  
    @Transactional(readOnly = true)  
    public DemoCity getDemoCityById(Integer id) throws  Exception{  
        //queryForObject:找不到会报异常  query:找不到则Null  
        //User user=jdbcTemplate.queryForObject("select id,name,email from users where id=?",new Object[]{id},new UserRowMapper());  
        List<DemoCity> cityList=jdbcTemplate.query("select ID,Name,CountryCode,District,Population from city where id=?",new Object[]{id},new DemoCityMapper());  
        DemoCity city=null;  
        if (!cityList.isEmpty()){  
        	city=cityList.get(0);  
        }  
        System.out.println(city);  
        return city;  
    }  
    
    /** 
     * 插入City信息
     * @param DemoCity 
     * @return 
     * @throws Exception 
     */  
    public int saveEmpty(final DemoCity city) throws  Exception{  
        int resRow=jdbcTemplate.update("INSERT INTO city( ID,Name,CountryCode,District,Population) VALUES(NULL,?,?,?,?)",new Object[]{  
        		city.getName(),city.getCountryCode(),city.getDistrict(),city.getPopulation()  
        });  
        return resRow;  
    }  
    
    /** 
     * 插入City数据-防止sql注入 
     * @param city 
     * @return 
     * @throws Exception 
     */  
    public int saveEmptyWithSafe(final DemoCity city) throws  Exception{  
        int resRow=jdbcTemplate.update("INSERT INTO city( ID,Name,CountryCode,District,Population) VALUES(NULL,?,?,?,?)", new PreparedStatementSetter() {  
            @Override  
            public void setValues(PreparedStatement ps) throws SQLException {  
                ps.setString(1,city.getName());  
                ps.setString(2,city.getCountryCode());  
                ps.setString(3,city.getDistrict());  
                ps.setString(4,city.getPopulation()); 
            }  
        });  
        return resRow;  
    }
    
    /** 
     * 插入City数据-防止sql注入-可以返回该条记录的主键（注意需要指定主键） 
     * @param empty 
     * @return 
     * @throws Exception 
     */  
    @Transactional(rollbackFor=UserException.class)  
    public int saveEmptyWithKey(final DemoCity city) throws  Exception{  
        String sql="INSERT INTO city( ID,Name,CountryCode,District,Population) VALUES(NULL,?,?,?,?)";  
        KeyHolder keyHolder=new GeneratedKeyHolder();  
        int resRow=jdbcTemplate.update(new PreparedStatementCreator() {  
            @Override  
            public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {  
                PreparedStatement ps=conn.prepareStatement(sql,new String[]{"ID"}); 		/*指定 id 为主键 */ 
                ps.setString(1,city.getName());  
                ps.setString(2,city.getCountryCode());  
                ps.setString(3,city.getDistrict());  
                ps.setString(4,city.getPopulation());   
                return ps;  
            }  
        },keyHolder);  
        return Integer.parseInt(keyHolder.getKey().toString());  
    }  
    
    /** 
     * 更新City信息 
     * @param city 
     * @return 
     */  
    public int updateCity(final DemoCity city) throws  Exception{  
        String sql="update city set Name=?,CountryCode=?,District=?,Population=? where ID=?";  
        int resRow=jdbcTemplate.update(sql, new PreparedStatementSetter() {  
            @Override  
            public void setValues(PreparedStatement preparedStatement) throws SQLException {  
            	preparedStatement.setString(1,city.getName());  
            	preparedStatement.setString(2,city.getCountryCode());  
            	preparedStatement.setString(3,city.getDistrict());  
            	preparedStatement.setString(4,city.getPopulation());
                preparedStatement.setInt(5,city.getId());  
            }  
        });  
        return resRow;  
    }  
    
    /** 
     * 删除city
     * @param City 
     * @return 
     * @throws Exception 
     */  
    public int deleteEmpty(final DemoCity city) throws  Exception{  
        int resRow=jdbcTemplate.update("DELETE FROM city WHERE ID=?", new PreparedStatementSetter() {  
            @Override  
            public void setValues(PreparedStatement ps) throws SQLException {  
                ps.setInt(1,city.getId());  
            }  
        });  
        return resRow;  
    }  
    
    /** 
     * 根据name查找city
     * @param city 
     * @return 
     * @throws Exception 
     */  
    public DemoCity getDemoCityByCityName(final DemoCity city) throws Exception{  
        String sql="select ID,Name,CountryCode,District,Population from city where Name=?";  
        List<DemoCity> queryList=jdbcTemplate.query(sql,new DemoCityMapper(),new Object[]{city.getName()});  
        if (queryList!=null && queryList.size()>0){  
            return queryList.get(0);  
        }else{  
            return null;  
        }  
    } 
    
    /** 
     * 获取记录数 
     * @return 
     * @throws Exception 
     */  
    public Integer getCount() throws  Exception{  
        String sql="select count(1) from city";  
        Integer total=jdbcTemplate.queryForObject(sql,Integer.class);  
        return total;  
    } 
}
